package com.lynn.springboot_demo.controller;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.lynn.springboot_demo.model.User;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;

/**
 * @author cl
 * @Date 2020/5/11 14:10
 */
@Controller
@RequestMapping
public class ExcelWriteController {

    @RequestMapping("/testPage")
    public String test() {
        return "test";
    }

    /**
     * 客户端下载导出Excel
     */
    @RequestMapping("/ExcelWriteServlet")
    public void ExcelWriteServlet(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //构造数据
        User user1 = new User(1, "老王", 20, "男");
        User user2 = new User(2, "老张", 25, "女");
        User user3 = new User(3, "老李", 80, "男");
        User user4 = new User(4, "老黄", 21, "女");

        ArrayList<User> users = CollUtil.newArrayList(user1, user2, user3, user4);

        // 通过工具类创建writer，默认创建xls格式
        //ExcelWriter writer = ExcelUtil.getWriter();
        //生成xlsx格式
        ExcelWriter writer = ExcelUtil.getWriter(true);
        //自定义标题别名（可以为标题起中文）
        writer.addHeaderAlias("id", "序号");
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("age", "年龄");
        writer.addHeaderAlias("sex", "性别");
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(3, "员工信息表");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(users, true);

        //xls格式
        //response为HttpServletResponse对象
        /*response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        response.setHeader("Content-Disposition", "attachment;filename=test.xls");*/

        //xlsx格式
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=test.xlsx");

        ServletOutputStream out = response.getOutputStream();
        writer.flush(out, true);
        // 关闭writer，释放内存
        writer.close();
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    }
}
